<?php
include(dirname(__FILE__)."/../Dao/db.php");
class InsertCVReportData
{
	var $objectDate ;
	
	
	function InsertCVReportData()
	{
		if(isset($_GET["objectDate"])) 
		{
			$this->objectDate = $_GET["objectDate"];
		}
		else
		{
			$this->objectDate = date("Y-m-d",mktime( date("H"), date("i")-10, date("s"), date("m"), date("d"), date("Y")));
		}
	}
	
	
	function run()
	{
		$this->getAdgroupCVData();
		$this->getKeywordsCVData();
		$this->getCreativeCVData();
	}
	
	/**
	 *  
	 */
	function getAdgroupCVData()
	{
		echo "AdcvReprt Table tally Begin: ".date("Y-m-d H:i:s")."\n";
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(1)\n";

		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt)
			SELECT memberId,listingType,camp_Id,adGr_Id,cvid,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."' 
			GROUP BY memberId,listingType,camp_Id,adgr_id,cvId;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.1)\n";
	
		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt)
			SELECT memberId,listingType,camp_Id,adGr_Id,-1,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."' 
			GROUP BY memberId,listingType,camp_Id,adgr_id;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.2)\n";
		
			
		$sql = " Delete from  adcvreport where date = '".$this->objectDate."' ;";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(3)\n";
		
		
		$sql = "
			INSERT INTO adcvreport(
				date,memberId,listingType,campaign_id,
				adgroup_Id,cvid,
				status,cv,sales,salesValue,clickCvspan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			)
			SELECT '".$this->objectDate."',adgroup.memberId,adgroup.listingType,adgroup.camp_id,
				adgroup.adGr_id,if(cvId is null,-1,cvId) as cvid,
			    adv.status,cv,sales,salesValue,clickCvSpan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			FROM adgroup
				left join  adcvreporttmp as adv
				on adgroup.adGr_id = adv.adgroup_Id
			WHERE adgroup.status <> 9 ;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(4)\n";
		
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(5)\n";
		
		echo "AdcvReprt Table tally END: ".date("Y-m-d H:i:s")."\n";
	}
	
	/**
	 *  
	 */
	function getKeywordsCVData(){
		echo "keywordscvreport Table tally Begin: ".date("Y-m-d H:i:s")."\n";
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(1)\n";

		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt,key_Id)
			SELECT memberId,listingType,camp_Id,adGr_Id,cvid,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt,key_Id 
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."'
				AND key_Id is not null 
			GROUP BY memberId,listingType,camp_Id,adgr_id,cvId,key_Id;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.1)\n";
		
		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt,key_Id)
			SELECT memberId,listingType,camp_Id,adGr_Id,-1,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt,key_Id 
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."'
				AND key_Id is not null 
			GROUP BY memberId,listingType,camp_Id,adgr_id,key_Id;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.2)\n";
			
		$sql = " Delete from  keywordscvreport where date = '".$this->objectDate."' ;";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(3)\n";
		
		
		$sql = "
			INSERT INTO keywordscvreport(
				date,memberId,listingType,campaign_id,
				adgroup_Id,keyword_Id,cvid,
				status,cv,sales,salesValue,clickCvspan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			)
			SELECT '".$this->objectDate."',keyword.memberId,keyword.listingType,keyword.camp_id,
				keyword.adGr_id,keyword.key_Id,if(cvId is null,-1,cvId) as cvid,
			    adv.status,cv,sales,salesValue,clickCvSpan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			FROM keyword
				left join  adcvreporttmp as adv
				on keyword.key_Id = adv.key_Id
			WHERE keyword.status <> 9 and keyword.negative = 0 ;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(4)\n";
		
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(5)\n";
		echo "keywordscvreport Table tally END: ".date("Y-m-d H:i:s")."\n";
	}
	
	/**
	 *  
	 */
	function getCreativeCVData(){
		echo "creativecvreport Table tally Begin: ".date("Y-m-d H:i:s")."\n";
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(1)\n";

		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt,Crt_Id)
			SELECT memberId,listingType,camp_Id,adGr_Id,cvid,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt,Crt_Id 
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."' 
				AND Crt_Id is not null
			GROUP BY memberId,listingType,camp_Id,adgr_id,cvId,Crt_Id;
			";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.1)\n";

		$sql = "	
			INSERT INTO adcvreporttmp(
				memberId,listingType,campaign_id,adgroup_Id,cvid,status,cv,sales,salesValue,
				clickCvspan,postCvCnt,
				directCvCnt,indirectCvCnt,
				asistCvCnt,Crt_Id)
			SELECT memberId,listingType,camp_Id,adGr_Id,-1,3,
				count(id) as cv,sum(sales) as sales,sum(sales)/count(sales) as salesValue,
				sum(clickCvSpan)/count(clickCvSpan) as clickCvSpan,sum(postCvFlag) as postCvCnt,
				sum(directCvFlag) as directCvCnt,sum(indirectCvFlag) as indirectCvCnt,
				sum(asistCvFlag) as asistCvCnt,Crt_Id 
			FROM cvtracking
			WHERE DATE_FORMAT(clickDate,'%Y-%m-%d') = '".$this->objectDate."' 
				AND Crt_Id is not null
			GROUP BY memberId,listingType,camp_Id,adgr_id,Crt_Id;
			";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(2.2)\n";		
			
		$sql = " Delete from  creativecvreport where date = '".$this->objectDate."' ;";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(3)\n";
		
		
		$sql = "
			INSERT INTO creativecvreport(
				date,memberId,listingType,campaign_id,
				adgroup_Id,creative_Id,cvid,
				status,cv,sales,salesValue,clickCvspan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			)
			SELECT '".$this->objectDate."',creative.memberId,creative.listingType,creative.camp_id,
				creative.adGr_id,creative.Crt_Id,if(cvId is null,-1,cvId) as cvid,
			    adv.status,cv,sales,salesValue,clickCvSpan,postCvCnt,directCvCnt,indirectCvCnt,asistCvCnt
			FROM creative
				left join  adcvreporttmp as adv
				on creative.Crt_Id = adv.Crt_Id
			WHERE creative.status <> 9 ;
			";
		//echo $sql;
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(4)\n";
		
		$sql = " Delete from  adcvreporttmp; ";
		mysql_query($sql);
		echo $this->objectDate."の合計：".mysql_affected_rows()."件処理完了(5)\n";
		echo "creativecvreport Table tally END: ".date("Y-m-d H:i:s")."\n";

	}
}

$instance = new InsertCVReportData;
$instance->run();

?>